﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;
namespace DAO
{
    public class ChuyenBayDAO
    {
        public List<ChuyenBayDTO> GetCB()
        {
            String sql = " select MaChuyenBay ";
            sql += "from ChuyenBay cb";            
            SqlConnection conn = DataProvider.ConnectDB("QLVCB.mdf");
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            List<ChuyenBayDTO> ds = new List<ChuyenBayDTO>();
            while (reader.Read())
            {
                ChuyenBayDTO cb = new ChuyenBayDTO();
                cb.MaCB = reader.GetString(0);       
                ds.Add(cb);
            }
            conn.Close();
            return ds;
        }
        public ChuyenBayDTO GetChitiet(string i)
        {
            String sql = "SELECT *";
            sql += "from ChuyenBay cb,TuyenBay tb";
            sql += " where MaChuyenBay like '"+i + "' AND";
            sql += " cb.MaTuyenBay = tb.MaTuyenBay"; 
            
            SqlConnection conn = DataProvider.ConnectDB("QLVCB.mdf");
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            //List<ChuyenBayDTO> ds = new List<ChuyenBayDTO>();
            ChuyenBayDTO cb = new ChuyenBayDTO();
            TuyenBayDTO tb = new TuyenBayDTO();
            while (reader.Read())
            {
                cb.MaCB = reader.GetString(0);
                cb.MaTB = reader.GetString(1);
                tb.SanBayDi = reader.GetString(7);
                tb.SanBayDen = reader.GetString(8);
                cb.TuyenBay = tb;
                cb.NgayGio = reader.GetDateTime(2);
                cb.ThoiGianBay = reader.GetInt16(3);
              
                //ds.Add(cb);
            }
            conn.Close();
            return cb;

        }

        #region Ngo Minh Nhat - source code

        public int LayMaCB()
        {
            String sql = "Select max(MaChuyenBay) from ChuyenBay";
            SqlConnection conn = DataProvider.ConnectDB("QLVCB.mdf");
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            int res = 0;
            string temp="";
            while(reader.Read())
                temp = reader.GetString(0);
            //string[] macb=temp.Split('C','B');
            //res = int.Parse(macb[0]);
            temp = temp.Substring(2);
            res = int.Parse(temp);
            conn.Close();
            return res;
        }

        public List<ChuyenBayDTO> TraCuuChuyenBay(int flag)
        {
            String sql = "select cb.MaChuyenBay, tb.SanBayDi,tb.SanBayDen, cb.NgayGio, cb.ThoiGianBay, SUM(ttv.SoGheTrong) as SoGheTrong, SUM(ttv.SoGheDat) as SoGheDat ";
            sql += "from ChuyenBay cb, TuyenBay tb, TinhTrangVe ttv ";
            sql += "where cb.MaTuyenBay=tb.MaTuyenBay and ";
            sql += "cb.MaChuyenBay=ttv.MaChuyenBay ";
            if (flag == 1)
                sql += "and DATEDIFF(day, cb.NgayGio, getdate())<1 ";
            sql += "group by cb.MaChuyenBay, tb.SanBayDi, tb.SanBayDen,cb.NgayGio, cb.ThoiGianBay";
            SqlConnection conn = DataProvider.ConnectDB("QLVCB.mdf");
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            List<ChuyenBayDTO> dsCB = new List<ChuyenBayDTO>();
            while (reader.Read())
            {
                ChuyenBayDTO cbdto = new ChuyenBayDTO();
                TuyenBayDTO tbdto = new TuyenBayDTO();
                cbdto.MaCB = reader.GetString(0);
                tbdto.SanBayDi = reader.GetString(1);
                tbdto.SanBayDen = reader.GetString(2);
                cbdto.TuyenBay = tbdto;
                //cbdto.SanbayDi = reader.GetString(1);
                //cbdto.sanbayDen = reader.GetString(2);
                cbdto.NgayGio = reader.GetDateTime(3);
                cbdto.ThoiGianBay = reader.GetInt16(4);
                cbdto.SoGheTrong = reader.GetInt32(5);
                cbdto.SoGheDat = reader.GetInt32(6);

                dsCB.Add(cbdto);
            }
            conn.Close();
            return dsCB;

        }

        

        public List<ChuyenBayDTO> TraCuuCBTheoYeuCau(ChuyenBayDTO dto, int flag)
        {
            String sql = "select cb.MaChuyenBay, tb.SanBayDi,tb.SanBayDen, cb.NgayGio, cb.ThoiGianBay, SUM(ttv.SoGheTrong) as SoGheTrong, SUM(ttv.SoGheDat) as SoGheDat ";
            sql += "from ChuyenBay cb, TuyenBay tb, TinhTrangVe ttv ";
            sql += "where cb.MaTuyenBay=tb.MaTuyenBay and ";
            sql += "cb.MaChuyenBay=ttv.MaChuyenBay ";
            if (dto.MaCB != null)
                sql += "and cb.MaChuyenBay like '%"+dto.MaCB+"%' ";

            if (dto.MaTB != null)
                sql += "and cb.MaTuyenBay = '" + dto.MaTB + "' ";

            if (dto.NgayGio.ToString("MM/dd/yyyy") != "01/01/0001")
            {
                sql += "and DATEDIFF(day, cb.NgayGio, '" + dto.NgayGio.ToString("MM/dd/yyyy HH:mm") + "')<1 ";
                sql += "and DATEDIFF(day, cb.NgayGio, '" + dto.NgayGio.ToString("MM/dd/yyyy HH:mm") + "')>=0 ";
            }
                
            if (flag == 1)
                sql += "and DATEDIFF(day, cb.NgayGio, getdate())<1 ";
            sql += "group by cb.MaChuyenBay, tb.SanBayDi, tb.SanBayDen,cb.NgayGio, cb.ThoiGianBay";
            SqlConnection conn = DataProvider.ConnectDB("QLVCB.mdf");
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            List<ChuyenBayDTO> dsCB = new List<ChuyenBayDTO>();
            while (reader.Read())
            {
                ChuyenBayDTO cbdto = new ChuyenBayDTO();
                TuyenBayDTO tbdto = new TuyenBayDTO();
                cbdto.MaCB = reader.GetString(0);
                tbdto.SanBayDi = reader.GetString(1);
                tbdto.SanBayDen = reader.GetString(2);
                cbdto.TuyenBay = tbdto;
                //cbdto.SanbayDi = reader.GetString(1);
                //cbdto.sanbayDen = reader.GetString(2);
                cbdto.NgayGio = reader.GetDateTime(3);
                cbdto.ThoiGianBay = reader.GetInt16(4);
                cbdto.SoGheTrong = reader.GetInt32(5);
                cbdto.SoGheDat = reader.GetInt32(6);

                dsCB.Add(cbdto);
            }
            conn.Close();
            return dsCB;
        }

        public string KiemTraTuyenBay(string matb)
        {
            String sql = "Select MaTuyenBay from TuyenBay where MaTuyenBay='" + matb + "'";
            SqlConnection conn = DataProvider.ConnectDB("QLVCB.mdf");
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            string res = "";
            while (reader.Read())
            {
                res = reader.GetString(0);
            }
            return res;
        }

        public int KiemTraThoiGianBay()
        {
            String sql = "Select ThoiGianBayToiThieu from ThamSo";
            SqlConnection conn = DataProvider.ConnectDB("QLVCB.mdf");
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            int res = 0;
            while (reader.Read())
            {
                res = reader.GetInt32(0);
            }
            return res;
        }

        public int ThemChuyenBay(ChuyenBayDTO cbdto)
        {
            //string NgayGio = cbdto.NgayGio.GetDateTimeFormats("dd);
            //String sql = string.Format(" insert into VeChuyenBay(MaChuyenBay,MaHangVe,MaHanhKhach,GiaTien) values ('{0}','{1}','{2}','{3}')", banve.MaChuyenBay, banve.MaHangVe, banve.MaHanhKhach, banve.GiaTien);
            String sql = string.Format(" insert into ChuyenBay values ('{0}','{1}','{2}','{3}','{4}','{5}')", cbdto.MaCB, cbdto.MaTB, cbdto.NgayGio.ToString("MM/dd/yyyy HH:mm"), cbdto.ThoiGianBay, cbdto.SoLuongGheHang1, cbdto.SoLuongGheHang2);
            //insert into ChuyenBay values ('CB01','HCM-HN','08/12/2011 06:00:00','60','50','100')
            SqlConnection conn = DataProvider.ConnectDB("QLVCB.mdf");
            SqlCommand cmd = new SqlCommand(sql, conn);
            int res = -1;
            res = cmd.ExecuteNonQuery();
            return res;
        }

        #endregion
    }
}
